﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL.Entities;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class ProveedorDAL : DBController
    {
        public ProveedorDAL()
        { 
        
        }

        /// <summary>
        /// Funcion que devuelve los proveedores
        /// </summary>
        /// <returns></returns>
        public List<ProveedorEntity> GetProveedores()
        {
            try
            {
                List<ProveedorEntity> proveedores = new List<ProveedorEntity>();
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spGetProveedores", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spGetProveedores";

                    dCmd.CommandType = CommandType.StoredProcedure;


                    // dCmd.ExecuteNonQuery();
                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            ProveedorEntity proveedor = new ProveedorEntity();
                            proveedor.IntIdProveedor = (int)dr.GetValue(0);
                            proveedor.VarCUIT = (string)dr.GetValue(1);
                            proveedor.VarDom = (string)dr.GetValue(2);
                            proveedor.VarMail = (dr.GetValue(3) == DBNull.Value) ? string.Empty : dr.GetValue(3).ToString();
                            proveedor.VarNombreContacto = (dr.GetValue(4) == DBNull.Value) ? string.Empty : dr.GetValue(3).ToString();
                            proveedor.VarRS = (string)dr.GetValue(5);
                            proveedor.IntIdCiudad = (int)dr.GetValue(6);
                            proveedor.IntIdRubro = (int)dr.GetValue(7);
                            proveedor.IntIdUsuario = (int)dr.GetValue(8);
                            proveedor.IntTelefono = long.Parse(dr.GetValue(9).ToString());
                            proveedor.BitIsDeleted = (bool)dr.GetValue(10);

                            proveedores.Add(proveedor);
                        }
                    }
                }
                return proveedores;

            }catch(Exception ex){
                throw ex;
            }
        }




        /// <summary>
        /// Funcion que devuelve un proveedor
        /// </summary>
        /// <returns></returns>
        public ProveedorEntity GetProveedorById(ProveedorEntity prov)
        {
            try
            {
                ProveedorEntity proveedor = new ProveedorEntity();
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spGetProveedorById", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spGetProveedorById";

                    dCmd.CommandType = CommandType.StoredProcedure;

                    dCmd.Parameters.AddWithValue("@intIdProveedor", prov.IntIdProveedor);
                    
                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();
                        dr.Read();
                        proveedor.IntIdProveedor = (int)dr.GetValue(0);
                        proveedor.VarRS = (string)dr.GetValue(1);
                        proveedor.VarDom = (string)dr.GetValue(2);
                        proveedor.IntTelefono = long.Parse(dr.GetValue(3).ToString());
                        proveedor.VarCUIT = (string)dr.GetValue(4);
                        proveedor.BitIsDeleted = (bool)dr.GetValue(5);
                        proveedor.IntIdCiudad = (int)dr.GetValue(6);
                        proveedor.IntIdRubro = (int)dr.GetValue(7);
                        proveedor.VarMail = (dr.GetValue(8) == DBNull.Value) ? string.Empty : dr.GetValue(8).ToString();
                        proveedor.VarNombreContacto = (dr.GetValue(9) == DBNull.Value) ? string.Empty : dr.GetValue(9).ToString();
                        proveedor.IntIdUsuario = (int)dr.GetValue(10);
                        proveedor.IntCodigoPostal = (int)dr.GetValue(11);
                }
                return proveedor;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        /// <summary>
        /// Funcion que devuelve un proveedor por su cuit
        /// </summary>
        /// <returns></returns>
        public ProveedorEntity GetProveedorByRazonSocial(ProveedorEntity prov)
        {
            try
            {
                ProveedorEntity proveedor = new ProveedorEntity();
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spGetProveedorByRazonSocial", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spGetProveedorByRazonSocial";

                    dCmd.CommandType = CommandType.StoredProcedure;

                    dCmd.Parameters.AddWithValue("@varRS", prov.VarRS);

                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();

                    if (dr.HasRows)
                    {
                        dr.Read();
                        proveedor.IntIdProveedor = (int)dr.GetValue(0);
                        proveedor.VarRS = (string)dr.GetValue(1);
                        proveedor.VarDom = (string)dr.GetValue(2);
                        proveedor.IntTelefono = long.Parse(dr.GetValue(3).ToString());
                        proveedor.VarCUIT = (string)dr.GetValue(4);
                        proveedor.BitIsDeleted = (bool)dr.GetValue(5);
                        proveedor.IntIdCiudad = (int)dr.GetValue(6);
                        proveedor.IntIdRubro = (int)dr.GetValue(7);
                        proveedor.VarMail = (dr.GetValue(8) == DBNull.Value) ? string.Empty : dr.GetValue(8).ToString();
                        proveedor.VarNombreContacto = (dr.GetValue(9) == DBNull.Value) ? string.Empty : dr.GetValue(9).ToString();
                        proveedor.IntIdUsuario = (int)dr.GetValue(10);
                        proveedor.IntCodigoPostal = (int)dr.GetValue(11);
                    }
                }
                return proveedor;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Funcion que devuelve un proveedor por su cuit
        /// </summary>
        /// <returns></returns>
        public ProveedorEntity GetProveedorByCuit(ProveedorEntity prov)
        {
            try
            {
                ProveedorEntity proveedor = new ProveedorEntity();
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spGetProveedorByCuit", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spGetProveedorByCuit";

                    dCmd.CommandType = CommandType.StoredProcedure;

                    dCmd.Parameters.AddWithValue("@varCUIT", prov.VarCUIT);

                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();
                    if (dr.HasRows)
                    {
                        dr.Read();
                        proveedor.IntIdProveedor = (int)dr.GetValue(0);
                        proveedor.VarRS = (string)dr.GetValue(1);
                        proveedor.VarDom = (string)dr.GetValue(2);
                        proveedor.IntTelefono = long.Parse(dr.GetValue(3).ToString());
                        proveedor.VarCUIT = (string)dr.GetValue(4);
                        proveedor.BitIsDeleted = (bool)dr.GetValue(5);
                        proveedor.IntIdCiudad = (int)dr.GetValue(6);
                        proveedor.IntIdRubro = (int)dr.GetValue(7);
                        proveedor.VarMail = (dr.GetValue(8) == DBNull.Value) ? string.Empty : dr.GetValue(8).ToString();
                        proveedor.VarNombreContacto = (dr.GetValue(9) == DBNull.Value) ? string.Empty : dr.GetValue(9).ToString();
                        proveedor.IntIdUsuario = (int)dr.GetValue(10);
                        proveedor.IntCodigoPostal = (int)dr.GetValue(11);
                    }
                }
                return proveedor;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Funcion que devuelve los proveedores filtrado
        /// </summary>
        /// <returns></returns>
        public List<ProveedorEntity> GetProveedoresFiltre(ProveedorEntity _proveedor)
        {
            try
            {
                List<ProveedorEntity> proveedores = new List<ProveedorEntity>();
                using (SqlConnection conn = this.OpenConn())
                {
                    //Armamos la query
                    string query = string.Empty;

                    query += "SELECT pr.intIdProveedor, pr.varCUIT, pr.varDom, pr.varMail, pr.varNombreContacto, pr.varRS, pr.intIdCiudad,pr.intIdRubro, pr.intIdUsuario, pr.intTelefono, pr.bitIsDeleted FROM MARAVILLA_SQL.Proveedores pr WHERE 1=1";
                    //Aplicamos los filtros
                    if (_proveedor.VarRS != null && _proveedor.VarRS != string.Empty)
                    {
                        query += " AND pr.varRS LIKE '%" + _proveedor.VarRS + "%'";
                    }


                    if (_proveedor.VarMail != null && _proveedor.VarMail != string.Empty)
                    {
                        query += " AND pr.varMail LIKE '%" + _proveedor.VarMail + "%'";
                    }

                    if (_proveedor.VarCUIT != null && _proveedor.VarCUIT != string.Empty)
                    {
                        query += " AND pr.varCUIT = '" + _proveedor.VarCUIT + "'";
                    }

                    SqlDataReader dr;

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spGetClientes", conn);

                    dCmd.CommandText = query;

                    dCmd.CommandType = CommandType.Text;


                    // dCmd.ExecuteNonQuery();
                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            ProveedorEntity proveedor = new ProveedorEntity();
                            proveedor.IntIdProveedor = (int)dr.GetValue(0);
                            proveedor.VarCUIT = (string)dr.GetValue(1);
                            proveedor.VarDom = (string)dr.GetValue(2);
                            proveedor.VarMail = (dr.GetValue(3) == DBNull.Value) ? string.Empty : dr.GetValue(3).ToString();
                            proveedor.VarNombreContacto = (dr.GetValue(4) == DBNull.Value) ? string.Empty : dr.GetValue(3).ToString();
                            proveedor.VarRS = (string)dr.GetValue(5);
                            proveedor.IntIdCiudad = (int)dr.GetValue(6);
                            proveedor.IntIdRubro = (int)dr.GetValue(7);
                            proveedor.IntIdUsuario = (int)dr.GetValue(8);
                            proveedor.IntTelefono = long.Parse(dr.GetValue(9).ToString());
                            proveedor.BitIsDeleted = (bool)dr.GetValue(10);

                            proveedores.Add(proveedor);
                        }
                    }
                }
                return proveedores;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        /// <summary>
        /// Funcion que borra un proveedor
        /// </summary>
        /// <returns></returns>
        public bool DeleteProveedor(Entities.ProveedorEntity proveedor)
        {
            try
            {
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spDeleteProveedor", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spDeleteProveedor";

                    dCmd.CommandType = CommandType.StoredProcedure;


                    dCmd.Parameters.AddWithValue("@intIdProveedor", proveedor.IntIdProveedor);

                    // dCmd.ExecuteNonQuery();
                    dCmd.ExecuteNonQuery();
                    dCmd.Dispose();
                }
                return true;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Funcion que agrega un proveedor
        /// </summary>
        /// <returns></returns>
        public Entities.ProveedorEntity AddProveedor(Entities.ProveedorEntity prov)
        {
            try
            {
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;
                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spAddProveedor", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spAddProveedor";

                    dCmd.CommandType = CommandType.StoredProcedure;
  
                    dCmd.Parameters.AddWithValue("@varRS", prov.VarRS);
                    dCmd.Parameters.AddWithValue("@varDom", prov.VarDom);
                    dCmd.Parameters.AddWithValue("@intTelefono", prov.IntTelefono);
                    dCmd.Parameters.AddWithValue("@intIdCiudad", prov.IntIdCiudad);
                    dCmd.Parameters.AddWithValue("@varCUIT", prov.VarCUIT);
                    dCmd.Parameters.AddWithValue("@intIdRubro", prov.IntIdRubro);
                    dCmd.Parameters.AddWithValue("@varMail", prov.VarMail);
                    dCmd.Parameters.AddWithValue("@varNombreContacto", prov.VarNombreContacto);
                    dCmd.Parameters.AddWithValue("@intCodigoPostal", prov.IntCodigoPostal);

                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();

                    dr.Read();

                    prov.IntIdProveedor = Int32.Parse(dr.GetValue(0).ToString());
                    prov.IntIdUsuario = Int32.Parse(dr.GetValue(1).ToString());

                }
                return prov;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        /// <summary>
        /// Funcion que cambia un proveedor A CLIENTE
        /// </summary>
        /// <returns></returns>
        public Entities.ClienteEntity CambiarProveedorACliente(Entities.ProveedorEntity prov ,Entities.ClienteEntity client)
        {
            try
            {
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;
                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spCambiarProveedorACliente", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spCambiarProveedorACliente";

                    dCmd.CommandType = CommandType.StoredProcedure;

                    dCmd.Parameters.AddWithValue("@intIdProveedor", prov.IntIdProveedor);
                    dCmd.Parameters.AddWithValue("@varNombre", client.VarNombre);
                    dCmd.Parameters.AddWithValue("@varApellido", client.VarApellido);
                    dCmd.Parameters.AddWithValue("@bigDNI", client.BigDNI);
                    dCmd.Parameters.AddWithValue("@varDireccion", client.VarDireccion);
                    dCmd.Parameters.AddWithValue("@bigTelefono", client.BigTelefono);
                    dCmd.Parameters.AddWithValue("@varMail", client.VarMail);
                    dCmd.Parameters.AddWithValue("@datFecha_Nac", client.DatFecha_Nac);
                    dCmd.Parameters.AddWithValue("@intCodigoPostal", client.IntCodigoPostal);

                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();

                    dr.Read();

                    client.IntIdCliente = Int32.Parse(dr.GetValue(0).ToString());
                    client.IntIdUsuario = Int32.Parse(dr.GetValue(1).ToString());

                }
                return client;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        
        /// <summary>
        /// Funcion que actualiza un proveedor
        /// </summary>
        /// <returns></returns>
        public bool UpdateProveedor(Entities.ProveedorEntity proveedor)
        {
            try
            {

                using (SqlConnection conn = this.OpenConn())
                {

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spUpdateProveedor", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spUpdateProveedor";

                    dCmd.CommandType = CommandType.StoredProcedure;

                    dCmd.Parameters.AddWithValue("@intIdProveedor", proveedor.IntIdProveedor);
                    dCmd.Parameters.AddWithValue("@varRS", proveedor.VarRS);
                    dCmd.Parameters.AddWithValue("@varDom", proveedor.VarDom);
                    dCmd.Parameters.AddWithValue("@intTelefono", proveedor.IntTelefono);
                    dCmd.Parameters.AddWithValue("@intIdCiudad", proveedor.IntIdCiudad);
                    dCmd.Parameters.AddWithValue("@intIdRubro", proveedor.IntIdRubro);
                    dCmd.Parameters.AddWithValue("@varMail", proveedor.VarMail);
                    dCmd.Parameters.AddWithValue("@varNombreContacto", proveedor.VarNombreContacto);
                    dCmd.Parameters.AddWithValue("@bitIsDeleted", proveedor.BitIsDeleted);
                    dCmd.Parameters.AddWithValue("@intCodigoPostal", proveedor.IntCodigoPostal);
                    
                    dCmd.ExecuteNonQuery();
                    dCmd.Dispose();
                }
                return true;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public ProveedorEntity GetProveedorByUserId(int idUsuario)
        {
            ProveedorEntity prov = new ProveedorEntity();

            using (SqlConnection conn = this.OpenConn())
            {

                SqlDataReader dr;

                SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spGetProveedorByUserId", conn);

                dCmd.CommandType = CommandType.StoredProcedure;

                dCmd.Parameters.AddWithValue("@idUsuario", idUsuario);

                dr = dCmd.ExecuteReader();
                dCmd.Dispose();

                if (dr.HasRows)
                {
                    dr.Read();
                    prov.IntIdProveedor = (int)dr.GetValue(0);
                    prov.VarRS = (string)dr.GetValue(1);
                    prov.VarDom = (string)dr.GetValue(2);
                    prov.IntTelefono = long.Parse(dr.GetValue(3).ToString());
                    prov.VarCUIT = (string)dr.GetValue(4);
                    prov.BitIsDeleted = (bool)dr.GetValue(5);
                    prov.IntIdCiudad = (int)dr.GetValue(6);
                    prov.IntIdRubro = (int)dr.GetValue(7);
                    prov.VarMail = (dr.GetValue(8) == DBNull.Value) ? string.Empty : dr.GetValue(8).ToString();
                    prov.VarNombreContacto = (dr.GetValue(9) == DBNull.Value) ? string.Empty : dr.GetValue(9).ToString();
                    prov.IntIdUsuario = (int)dr.GetValue(10);
                    prov.IntCodigoPostal = (int)dr.GetValue(11);
                }
            }

            return prov;
        }

        public void CanjearCupon(DateTime fechaActual, int idCompra)
        {
            using (SqlConnection conn = this.OpenConn())
            {
                SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spCanjearCupon", conn);

                dCmd.CommandType = CommandType.StoredProcedure;

                dCmd.Parameters.AddWithValue("@fechaActual", fechaActual);
                dCmd.Parameters.AddWithValue("@idCompra", idCompra);

                dCmd.ExecuteNonQuery();
                dCmd.Dispose();
            } 
        }

    }
}
